﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace DAL
{
    public class SaveRepository:ISaveRepository
    {
        //public static string connectionString = ConfigurationManager.ConnectionStrings["projectbConnection"].ToString();
        private SqlConnection conn;
        public SaveRepository(SqlConnection sqlconnnection)
        {
            this.conn = sqlconnnection;
        }
        /// <summary>
        /// save course
        /// </summary>
        public int insertCourse(Model.Course course)
        {
            DataSet ds = new DataSet();
            
                try
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("insert into Course(CourseName,TeacherId,LevelId,CourseStatus)values(@CourseName,@TeacherId,@LevelId,@CourseStatus);select SCOPE_IDENTITY()", conn);
                    comm.CommandType = CommandType.Text;
                    SqlParameter[] paras = new SqlParameter[]
                    {   
                        new SqlParameter("CourseName",course.CourseName),
                        new SqlParameter("TeacherId",course.TeacherId),
                        new SqlParameter("LevelId",course.LevelId),
                        new SqlParameter("CourseStatus",course.CourseStatus)
                    };
                    comm.Parameters.AddRange(paras);
                    //comm.ExecuteNonQuery();
                    //conn.Open();
                    int courseId = Convert.ToInt32(comm.ExecuteScalar());
                    //conn.Close();
                    return courseId;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
        
        /// <summary>
        /// update 
        /// </summary>
        /// <param name="course"></param>
        public void updateCourse(Model.Course course)
        {
            DataSet ds = new DataSet();
            
                try
                {
                    conn.Open();

                    SqlCommand comm = new SqlCommand("update Course set CourseName=@CourseName,TeacherId=@TeacherId,LevelId=@LevelId,CourseStatus=@CourseStatus where CourseId=@CourseId", conn);
                    comm.CommandType = CommandType.Text;
                    SqlParameter[] paras = new SqlParameter[]
                    {   
                        new SqlParameter("CourseId",course.CourseId),
                        new SqlParameter("CourseName",course.CourseName),
                        new SqlParameter("TeacherId",course.TeacherId),
                        new SqlParameter("LevelId",course.LevelId),
                        new SqlParameter("CourseStatus",course.CourseStatus)
                    };
                    comm.Parameters.AddRange(paras);
                    comm.ExecuteNonQuery();

                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
        
        public void insertCourseDetails(Model.CourseDetails cd)
        {
            DataSet ds = new DataSet();
            
                try
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("insert into CourseDetails(CourseId,StudentId)values(@CourseId,@StudentId)", conn);
                    comm.CommandType = CommandType.Text;
                    SqlParameter[] paras = new SqlParameter[]
                    {   
                        new SqlParameter("CourseId",cd.CourseId),
                        new SqlParameter("StudentId",cd.StudentId), 
                    };
                    comm.Parameters.AddRange(paras);
                    comm.ExecuteNonQuery();

                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
        

        public void deleteCourseDetails(int cID)
        {
            
                conn.Open();
                SqlCommand comm = new SqlCommand("delete from CourseDetails where CourseId=" + cID, conn);
                comm.ExecuteNonQuery();
                conn.Close();
            
        }
        public List<Model.Course> getCourseName(int courseId)
        {
            DataSet ds = new DataSet();
            List<Model.Course> course = new List<Model.Course>();
            

                try
                {
                    conn.Open();
                    SqlCommand comm = new SqlCommand("select CourseName from Course where CourseId=" + courseId, conn);
                    SqlDataAdapter da = new SqlDataAdapter(comm);
                    da.Fill(ds);
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        Model.Course c = new Model.Course
                        {
                            CourseName = row[0].ToString()
                        };
                        course.Add(c);
                    }
                    return course;
                }
                catch (SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }

    }
    


